﻿Imports System.Data
Imports System.Data.SqlClient
Public Class CaixaDAL
#Region "Métodos Localizar"
    Function localizar(ByVal codigo As Integer) As DataTable 'Código construtor
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Ofi_Caixa where cai_codigo= @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function carregadg() As DataTable 'Data grid
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinCaixa order by sta_descricao"
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function locadata(ByVal dtini As Date, ByVal dtfim As Date) As DataTable 'Filtro datas
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinCaixa where cai_dtabertura BETWEEN @dtini AND @dtfim"
            cmd.Parameters.Add(New SqlParameter("@dtini", SqlDbType.Date)).Value = Format(dtini, "yyyy/MM/dd")
            cmd.Parameters.Add(New SqlParameter("@dtfim", SqlDbType.Date)).Value = Format(dtfim, "yyyy/MM/dd")
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function


#End Region
#Region "Inserir"
    Function inserir(ByVal caidescr As String, ByVal dtabertura As Date, ByVal dtfechamento As Nullable(Of Date), ByVal caixainicial As Decimal, ByVal caixafinal As Decimal, ByVal cxfechamento As Decimal, ByVal usuario As Integer, ByVal status As Integer, ByVal res As Double) As Integer
        Dim cmd As SqlCommand
        Dim codigo As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "insert into Financeiro.Ofi_Caixa(cai_descricao, cai_dtabertura,cai_dtfechamento,cai_valorinicial,cai_valorfinal,cai_fechamento,usu_codigo,sta_codigo,cai_resultado)" & _
                "values(@cxdesc,@dtabert,@dtfecham,@cxinicial,@cxfinal,@cxfechamento,@usuario,@status,@res)"
            cmd.Parameters.Add(New SqlParameter("@cxdesc", SqlDbType.VarChar)).Value = caidescr
            cmd.Parameters.Add(New SqlParameter("@dtabert", SqlDbType.Date)).Value = dtabertura
            If (dtfechamento.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@dtfecham", SqlDbType.Date)).Value = dtfechamento
            Else
                cmd.Parameters.Add(New SqlParameter("@dtfecham", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@cxinicial", SqlDbType.Decimal)).Value = caixainicial
            cmd.Parameters.Add(New SqlParameter("@cxfinal", SqlDbType.Decimal)).Value = caixafinal
            cmd.Parameters.Add(New SqlParameter("@cxfechamento", SqlDbType.Decimal)).Value = cxfechamento
            cmd.Parameters.Add(New SqlParameter("@usuario", SqlDbType.Int)).Value = usuario
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
            cmd.Parameters.Add(New SqlParameter("@res", SqlDbType.Decimal)).Value = res
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw New Exception("", ex)
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
#Region "ALterar"
    Function alterar(ByVal codigo As Integer, caidescr As String, ByVal dtabertura As Date, ByVal dtfechamento As Nullable(Of Date), ByVal caixainicial As Decimal, ByVal caixafinal As Decimal, ByVal cxfechamento As Decimal, ByVal usuario As Integer, ByVal status As Integer, ByVal res As Double) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "update Financeiro.Ofi_Caixa set cai_descricao=@cxdesc,cai_dtabertura=@dtabert,cai_dtfechamento=@dtfecham,cai_valorinicial=@cxinicial,cai_valorfinal=@cxfinal,cai_fechamento=@cxfechamento,usu_codigo=@usuario,sta_codigo=@status,cai_resultado=@res  " & _
             " where cai_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@cxdesc", SqlDbType.VarChar)).Value = caidescr
            cmd.Parameters.Add(New SqlParameter("@dtabert", SqlDbType.Date)).Value = dtabertura
            If (dtfechamento.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@dtfecham", SqlDbType.Date)).Value = dtfechamento
            Else
                cmd.Parameters.Add(New SqlParameter("@dtfecham", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@cxinicial", SqlDbType.Decimal)).Value = caixainicial
            cmd.Parameters.Add(New SqlParameter("@cxfinal", SqlDbType.Decimal)).Value = caixafinal
            cmd.Parameters.Add(New SqlParameter("@cxfechamento", SqlDbType.Decimal)).Value = cxfechamento
            cmd.Parameters.Add(New SqlParameter("@usuario", SqlDbType.Int)).Value = usuario
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
            cmd.Parameters.Add(New SqlParameter("@res", SqlDbType.Decimal)).Value = res
            ret = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region
#Region "VALIDA ABERTURA"
    Function validaAbertura() As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_FinCaixa where sta_codigo=4 "
            tab = objconexao.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "BUSCAR CBFILTRO"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo

                Case 0
                    cmd.CommandText = "select * from vw_FinCaixa where cai_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 1
                    cmd.CommandText = "select * from vw_FinCaixa where cai_dtabertura= @abertura order by cai_dtabertura=@dtabertura"
                    cmd.Parameters.Add(New SqlParameter("@abertura", SqlDbType.Date)).Value = Format(Descricao, "yyyy/MM/dd")
                Case 2
                    cmd.CommandText = "select * from vw_FinCaixa where sta_descricao = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
                Case 3
                    cmd.CommandText = "select * from vw_FinCaixa order by sta_descricao asc"
                Case Else
                    cmd.CommandText = "select * from vw_FinCaixa where sta_codigo=4"
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Soma"
    Function SomaSuplemento(ByVal caixa As Integer) As Decimal ''sumplemento
        Dim cmd As SqlCommand
        Dim ret As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select SUM(itec_valor) soma from Financeiro.Ofi_ItensCaixa where sta_codigo=7 and cai_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = caixa
            ret = objconexao.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        If (ret.Rows.Count > 0 And ret(0)("soma").ToString.Trim <> "") Then
            Return Decimal.Parse(ret(0)("soma").ToString)
        Else : Return 0
        End If
    End Function
    Function SomaSangria(ByVal codigo As Integer) As Decimal ''sangria
        Dim cmd As SqlCommand
        Dim ret As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select SUM(itec_valor) soma from Financeiro.Ofi_ItensCaixa where sta_codigo=8 and cai_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        If (ret.Rows.Count > 0 And ret(0)("soma").ToString.Trim <> "") Then
            Return Decimal.Parse(ret(0)("soma").ToString)
        Else : Return 0
        End If
    End Function

    Function SomaVendas(ByVal codigo As Integer) As Decimal 'Vendas
        Dim cmd As SqlCommand
        Dim ret As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select SUM(ven_total) soma from vw_venVenda where cai_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        If (ret.Rows.Count > 0 And ret(0)("soma").ToString.Trim <> "") Then
            Return Decimal.Parse(ret(0)("soma").ToString)
        Else : Return 0
        End If
    End Function
#End Region
End Class
